package com.octopus.kernal.dao;

import java.io.Serializable;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.LockMode;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Example;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.octopus.kernal.controller.AbstractSearchCommand;
import com.octopus.kernal.model.BaseStruct;
import com.octopus.kernal.service.BusinessModelService;
import com.octopus.kernal.util.xml.Entity;
import com.octopus.kernal.controller.SearchOperator;

/**
 * GenericHibernateDao extends HibernateDaoSupport, encapsulate functionalities of HibernateTemplate, facilitate Hibernate DAO coding
 */
@SuppressWarnings("unchecked")
public class GenericHibernateDao<T extends BaseStruct, PK extends Serializable> extends HibernateDaoSupport implements GenericDao<T, PK> {
	
	@SuppressWarnings("unused")
	private static Log log = LogFactory.getLog(GenericHibernateDao.class);
	
	@Autowired  
	private BusinessModelService businessModelService;
	
	@Autowired  
	public void setSessionFactory0(SessionFactory sessionFactory){  
	  super.setSessionFactory(sessionFactory);  
	}  
	
	private Class<T> entityClass;

	public GenericHibernateDao() {
		this.entityClass = null;
		this.setEntityClass(this.getClass());
	}
	
	public GenericHibernateDao(Class<T> entityClass) {
		this.entityClass = entityClass;
	}
	
	private void setEntityClass(Class<?> clazz){
		Type t = clazz.getGenericSuperclass();
		if (t instanceof ParameterizedType) {
			Type[] p = ((ParameterizedType) t).getActualTypeArguments();
			this.entityClass = (Class<T>) p[0];
		}else{
			Class<?> superClazz = clazz.getSuperclass();
			if(!superClazz.equals(GenericDao.class) && !superClazz.equals(Object.class)){
				this.setEntityClass(superClazz);
			}
		}
	}

	// -------------------- Basic query, insert, update, update --------------------

	// Get entity by PK, if no entity , return null.
	public T get(PK id) {
		return (T) getHibernateTemplate().get(entityClass, id);
	}

	// Get entity and add lock by PK, if no entity, return null.
	public T getWithLock(PK id, LockMode lock) {
		T t = (T) getHibernateTemplate().get(entityClass, id, lock);
		if (t != null) {
			this.flush(); // Flush immediately, otherwise lock can not take effect.
		}
		return t;
	}
	
	// Load entity by PK, if no entity, throw exception.
	public T load(PK id) {
		return (T) getHibernateTemplate().load(entityClass, id);
	}

	// Load entity and add lock by PK, if no entity, throw exception.
	public T loadWithLock(PK id, LockMode lock) {
		T t = (T) getHibernateTemplate().load(entityClass, id, lock);
		if (t != null) {
			this.flush(); // Flush immediately, otherwise lock can not take effect.
		}
		return t;
	}

	// Load all entities.
	public List<T> loadAll() {
		return (List<T>) getHibernateTemplate().loadAll(entityClass);
	}
	
	// Load by pagination
	public List<T> loadAll(final int offset, final int length) {
		return getHibernateTemplate().executeWithNativeSession(new HibernateCallback<List<T>>() {
			public List<T> doInHibernate(Session session) throws HibernateException {
				Criteria criteria = session.createCriteria(entityClass);
				criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
				criteria.setFirstResult(offset);
                criteria.setMaxResults(length);
				return criteria.list();
			}
		});
	}

	// Update entity
	public void update(T entity) {
		getHibernateTemplate().update(entity);
	}

	// Update entity and add lock
	public void updateWithLock(T entity, LockMode lock) {
		getHibernateTemplate().update(entity, lock);
		this.flush(); // Flush immediately, otherwise lock can not take effect.
	}

	// Save entity
	public void save(T entity) {
		getHibernateTemplate().save(entity);
	}

	// Save or update entity
	public void saveOrUpdate(T entity) {
		getHibernateTemplate().saveOrUpdate(entity);
	}

	// Save or update all entities in the cllection
	public void saveOrUpdateAll(Collection<T> entities) {
		getHibernateTemplate().saveOrUpdateAll(entities);
	}

	// Delete entity
	public void delete(T entity) {
		getHibernateTemplate().delete(entity);
	}

	// Delete and add lock of entity
	public void deleteWithLock(T entity, LockMode lock) {
		getHibernateTemplate().delete(entity, lock);
		this.flush(); // Flush immediately, otherwise lock can not take effect.
	}

	// Delete entity by PK
	public void deleteByKey(PK id) {
		this.delete(this.load(id));
	}

	// Delete entity and add lock by PK
	public void deleteByKeyWithLock(PK id, LockMode lock) {
		this.deleteWithLock(this.load(id), lock);
	}

	// Delete all entities in the collection
	public void deleteAll(Collection<T> entities) {
		getHibernateTemplate().deleteAll(entities);
	}

	// -------------------- HSQL ----------------------------------------------

	// Save, update or delete entities by HSQL
	public int bulkUpdate(String queryString) {
		return getHibernateTemplate().bulkUpdate(queryString);
	}

	// Save, update or delete entities by HSQL with parameters
	public int bulkUpdate(String queryString, Object[] values) {
		return getHibernateTemplate().bulkUpdate(queryString, values);
	}

	// Find entities by HSQL
	public List<T> find(String queryString) {
		return getHibernateTemplate().find(queryString);
	}

	// Find entities by HSQL with parameters
	public List<T> find(String queryString, Object[] values) {
		return getHibernateTemplate().find(queryString, values);
	}
	
	// Find with pagination
	public List<T> find(final String queryString, final Object[] values, final int offset, final int length) {
		List<T> list = getHibernateTemplate().executeFind(
				new HibernateCallback<List<T>>() {
					public List<T> doInHibernate(Session session) throws HibernateException, SQLException {
						Query query = session.createQuery(queryString);
						query.setFirstResult(offset);
						query.setMaxResults(length);
						if (values != null) {
							for (int i = 0; i < values.length; i++) {
								query.setParameter(i, values[i]);
							}
						}
						List<T> l = query.list();
						return l;
					}
				});
		return list;
	}
	
	// Count
	@SuppressWarnings("rawtypes")
	public int count(final String queryString, final Object[] values) {
		List list = getHibernateTemplate().executeFind(
				new HibernateCallback<List>() {
					public List doInHibernate(Session session) throws HibernateException, SQLException {
						Query query = session.createQuery(queryString);
						if (values != null) {
							for (int i = 0; i < values.length; i++) {
								query.setParameter(i, values[i]);
							}
						}
						List l = query.list();
						return l;
					}
				});
		return ((Long)list.get(0)).intValue();
	}

	// Find entities by HSQL with named parameters
	public List<T> findByNamedParam(String queryString, String[] paramNames, Object[] values) {
		return getHibernateTemplate().findByNamedParam(queryString, paramNames, values);
	}
	
	// Find entities by HSQL by example
	public List<T> findByExample(T t) {
		return getHibernateTemplate().findByExample(t);
	}

	// Find entities by named HSQL
	public List<T> findByNamedQuery(String queryName) {
		return getHibernateTemplate().findByNamedQuery(queryName);
	}

	// Find entities by named HSQL with parameters
	public List<T> findByNamedQuery(String queryName, Object[] values) {
		return getHibernateTemplate().findByNamedQuery(queryName, values);
	}

	// Find entities by named HSQL with named parameters
	public List<T> findByNamedQueryAndNamedParam(String queryName, String[] paramNames, Object[] values) {
		return getHibernateTemplate().findByNamedQueryAndNamedParam(queryName, paramNames, values);
	}

	// Find entities by HSQL, return iterator
	public Iterator<T> iterate(String queryString) {
		return getHibernateTemplate().iterate(queryString);
	}

	// Find entities by HSQL with parameters, return iterator
	public Iterator<T> iterate(String queryString, Object[] values) {
		return getHibernateTemplate().iterate(queryString, values);
	}

	// Close iterator
	public void closeIterator(Iterator<T> it) {
		getHibernateTemplate().closeIterator(it);
	}

	// -------------------------------- Criteria ------------------------------

	// Create criteria without session
	public DetachedCriteria createDetachedCriteria() {
		return DetachedCriteria.forClass(this.entityClass);
	}

	// Create criteria binded with session
	public Criteria createCriteria() {
		return this.createDetachedCriteria().getExecutableCriteria(this.getSession());
	}

	// Find entities with criteria
	public List<T> findByCriteria(DetachedCriteria criteria) {
		criteria.setProjection(Projections.distinct(Projections.id())); 
		List<Object> list = getHibernateTemplate().findByCriteria(criteria);
		List<T> result = new ArrayList<T>();
		if(list != null){
			for(Object obj : list){
				result.add(this.get((PK)obj));
			}
		}
		return result;
	}
	
	// Find entities with criteria
	public List<T> findByCriteria(DetachedCriteria criteria, String[] propertyNames) {
		ProjectionList plist = Projections.projectionList().add(Projections.id());
		if(propertyNames != null){
			for(String p : propertyNames){
				String property = p;
				if(property.indexOf(":") > 0){
					property = property.substring(0, property.indexOf(":"));
				}
				plist.add(Projections.property(property));
			}
		}
		criteria.setProjection(Projections.distinct(plist));
		List<Object> list = getHibernateTemplate().findByCriteria(criteria);
		List<T> result = new ArrayList<T>();
		if(list != null){
			for(Object obj : list){
				if(obj instanceof Object[]){
					result.add(this.get((PK)((Object[])obj)[0]));
				}else{
					result.add(this.get((PK)obj));
				}
			}
		}
		return result;
	}

	// Find entities with criteria, with scoped result
	public List<T> findByCriteria(DetachedCriteria criteria, int firstResult, int maxResults) {
		criteria.setProjection(Projections.distinct(Projections.id()));
		List<Object> list = getHibernateTemplate().findByCriteria(criteria, firstResult, maxResults);
		List<T> result = new ArrayList<T>();
		if(list != null){
			for(Object obj : list){
				result.add(this.get((PK)obj));
			}
		}
		return result;
	}
	
	// Find entities with criteria, with scoped result
	public List<T> findByCriteria(DetachedCriteria criteria, String[] propertyNames, int firstResult, int maxResults) {
		ProjectionList plist = Projections.projectionList().add(Projections.id());
		if(propertyNames != null){
			for(String p : propertyNames){
				String property = p;
				if(property.indexOf(":") > 0){
					property = property.substring(0, property.indexOf(":"));
				}
				plist.add(Projections.property(property));
			}
		}
		criteria.setProjection(Projections.distinct(plist));
		List<Object> list = getHibernateTemplate().findByCriteria(criteria, firstResult, maxResults);
		List<T> result = new ArrayList<T>();
		if(list != null){
			for(Object obj : list){
				if(obj instanceof Object[]){
					result.add(this.get((PK)((Object[])obj)[0]));
				}else{
					result.add(this.get((PK)obj));
				}
			}
		}
		return result;
	}
	
	//  Count entities with certain entity and properties (meet property=value,except PK)
	public Integer countEqualByEntity(T entity) {
		DetachedCriteria criteria = this.detachedCriteriaOfFindByExample(entity, null, false);
		return this.getRowCount(criteria);
	}
	//  Find entities with certain entity and properties (meet property=value,except PK)
	public List<T> findEqualByEntity(T entity, String[] propertyNames) {
		return this.findEqualByEntity(entity, propertyNames, -1, -1);
	}
	
	//  Find entities with certain entity and properties (meet property=value,except PK)
	public List<T> findEqualByEntity(T entity, String[] propertyNames, int offset, int length) {
		DetachedCriteria criteria = this.detachedCriteriaOfFindByExample(entity, propertyNames, false);

		if(offset == -1 || length == -1){
			return this.findByCriteria(criteria, propertyNames);
		}else{
			return this.findByCriteria(criteria, propertyNames, offset, length);
		}
	}
	
	private DetachedCriteria detachedCriteriaOfFindByExample(T entity, String[] propertyNames, boolean like) {
		Example example = null==entity?null:Example.create(entity);
		DetachedCriteria criteria = this.createDetachedCriteria();
		if(example != null){
			example.excludeZeroes();
			if(like)
				example.enableLike(MatchMode.ANYWHERE);
		}
		// Reference
		if(entity != null)
			this.setReferenceCriteria(criteria, entity, like);
		// Sort
		if(propertyNames != null){
			String[] defPropertys = getSessionFactory().getClassMetadata(entityClass).getPropertyNames();
			for (String defProperty : defPropertys) {
				int ii = 0;
				for (ii = 0; ii < propertyNames.length; ++ii) {
					String ps = propertyNames[ii];
					String property = ps;
					String sort = "asc";
					if(ps.indexOf(":") > 0){
						property = ps.substring(0, ps.indexOf(":"));
						sort = ps.substring(ps.indexOf(":") + 1);
					}
					if (defProperty.equals(property)) {
						if(sort.equals("asc"))
							criteria.addOrder(Order.asc(defProperty));
						else if(sort.equals("desc"))
							criteria.addOrder(Order.desc(defProperty));
						break;
					}
				}
				if (example != null && ii == propertyNames.length) {
					example.excludeProperty(defProperty);
				}
			}
		}
		if(example != null){
			criteria.add(example);
		}
		return criteria;
	}
	
	private void setReferenceCriteria(DetachedCriteria criteria, Object entity, boolean like){
		Field[] fields = entity.getClass().getDeclaredFields();
		for(Field field : fields){
			if(!field.getType().equals(Set.class) && !field.getType().equals(BaseStruct.class) || field.getName().equals("customizedStruct")){
				continue;
			}
			Method method = null;
			Object value = null;
			try {
				method = entity.getClass().getMethod("get" + StringUtils.capitalize(field.getName()));
				value = method.invoke(entity);
				if(value == null)
					continue;
			} catch (Exception e) {
				e.printStackTrace();
			}
			Object refEntity = null;
			if(field.getType().equals(Set.class)){
				Set<Object> set = (Set<Object>)value;
				if(set.size() == 0)
					continue;
				refEntity = set.toArray()[0];
			}else if(field.getType().equals(BaseStruct.class)){
				refEntity = value;

			}
			if(refEntity != null){
				Example refExample = Example.create(refEntity);
				if(like)
					refExample.enableLike(MatchMode.ANYWHERE);
				DetachedCriteria subCriteria = criteria.createCriteria(field.getName());
				subCriteria.add(refExample);
				this.setReferenceCriteria(subCriteria, refEntity, like);
			}
		}
	}
	
	//  Count entities with certain entity and properties (meet property like)
	public Integer countLikeByEntity(T entity) {
		return this.getRowCount(this.detachedCriteriaOfFindByExample(entity, null, true));
	}
	
	//  Find entities with certain entity and properties (meet property like)
	public List<T> findLikeByEntity(T entity, String[] propertyNames) {
		return this.findLikeByEntity(entity, propertyNames, -1, -1);
	}

	//  Find entities with certain entity and properties (meet property like)
	public List<T> findLikeByEntity(T entity, String[] propertyNames, int offset, int length) {
		DetachedCriteria criteria = this.detachedCriteriaOfFindByExample(entity, propertyNames, true);
		if(offset == -1 || length == -1){
			return this.findByCriteria(criteria, propertyNames);
		}else{
			return this.findByCriteria(criteria, propertyNames, offset, length);
		}
	}

	//  Get row number with criteria
	@SuppressWarnings("rawtypes")
	public Integer getRowCount(DetachedCriteria criteria) {
		criteria.setProjection(Projections.rowCount());
		List list = getHibernateTemplate().findByCriteria(criteria);
		return (Integer) list.get(0);
	}

	// Find entities with criteria, return (max,min,avg,sum)
	@SuppressWarnings("rawtypes")
	public Object getStatValue(DetachedCriteria criteria, String propertyName,
			String StatName) {
		if (StatName.toLowerCase().equals("max"))
			criteria.setProjection(Projections.max(propertyName));
		else if (StatName.toLowerCase().equals("min"))
			criteria.setProjection(Projections.min(propertyName));
		else if (StatName.toLowerCase().equals("avg"))
			criteria.setProjection(Projections.avg(propertyName));
		else if (StatName.toLowerCase().equals("sum"))
			criteria.setProjection(Projections.sum(propertyName));
		else
			return null;
		List list = this.findByCriteria(criteria, 0, 1);
		return list.get(0);
	}
	
	// -------------------------------- SQL --------------------------------
	public void executeSQL(final String sql, final Map<String, Object> params){
		getHibernateTemplate().execute(new HibernateCallback<Object>() {
			public Object doInHibernate(Session s) throws HibernateException, SQLException {
				SQLQuery query = s.createSQLQuery(sql);
				if(params != null){
					for(String key : params.keySet())
						query.setParameter(key, params.get(key));
				}
				query.executeUpdate();
				return null;
			}
		});
	}

	// -------------------------------- Others --------------------------------

	// Lock entity
	public void lock(T entity, LockMode lock) {
		getHibernateTemplate().lock(entity, lock);
	}

	// Initialize entity
	public void initialize(Object proxy) {
		getHibernateTemplate().initialize(proxy);
	}

	// Flush
	public void flush() {
		getHibernateTemplate().flush();
	}
	
	public int searchCount(AbstractSearchCommand<T> search){
		int number = 0;
		Entity entity = businessModelService.getEntityByClass(entityClass);
		StringBuffer sql = new StringBuffer("select count(" + entity.getAlias() + ") from " + entity.getName() + "Struct " + entity.getAlias());
		List<Object> values = new ArrayList<Object>();
		if(search == null){
			number = count(sql.append(" where " + entity.getAlias() + ".id > 0").toString(), values.toArray());
			return number;
		}
		this.prepareHSQLWhere(search, sql, values);
		number = count(sql.toString(), values.toArray());
		search.setTotalNumber(number);
		return number;
	}
	
	public List<T> search(AbstractSearchCommand<T> search){
		Entity entity = businessModelService.getEntityByClass(entityClass);
		List<T> list = null;
		StringBuffer sql = new StringBuffer("select distinct " + entity.getAlias() + " from " + entity.getName() + "Struct " + entity.getAlias());
		List<Object> values = new ArrayList<Object>();
		if(search == null)
			return loadAll();
		this.prepareHSQLWhere(search, sql, values);
		this.prepareHSQLOrder(search, sql, values);
		if(search.getOffset() != null && search.getMaxPageItems() != null)
			list = find(sql.toString(), values.toArray(), search.getOffset(), search.getMaxPageItems());
		else
			list = find(sql.toString(), values.toArray());
		return list==null?new ArrayList<T>():list;
	}
	
	@SuppressWarnings("rawtypes")
	private void prepareHSQLWhere(AbstractSearchCommand<T> search, StringBuffer sql, List<Object> values){
		Entity entity = businessModelService.getEntityByClass(entityClass);
		T struct = search.getEntity();
		T struct2 = search.getEntity2();
		T operator = search.getOperator();
		StringBuffer condition = new StringBuffer(" where 1=1");
		Map<String, List<String>> tables = new HashMap<String, List<String>>();
		// Check fields
		if(operator != null){
			for(com.octopus.kernal.util.xml.Field field : entity.getFields()){
				String type = field.getColumn().getType();
//				if(field.getElementSearch() == null)
//					continue;
				Object value = struct.getAttributeValues().get(field.getName());
				Object value2 = struct2==null?null:struct2.getAttributeValues().get(field.getName());
				Object valueo = operator==null?null:operator.getAttributeValues().get(field.getName());
				if(type.equals("Date") || type.equals("Time")){
					valueo = operator.getAttributeValues().get(field.getName() + "Str");
				}
				if(valueo == null)
					continue;
				String valueos = null;
				if(type.equals("String")){
					if(StringUtils.isBlank((String)value))
						continue;
					valueos = String.valueOf(valueo);
				}else if(type.equals("Long") || type.equals("Integer")){
					valueos = String.valueOf(valueo);
				}else if(type.equals("Date") || type.equals("Time")){
					valueos = String.valueOf(valueo);
				}else if(type.equals("Boolean")){
					valueos = String.valueOf(SearchOperator.EQUAL.ordinal());
				}else{
					if(value != null && value instanceof BaseStruct){
						
					}else if(value != null && value instanceof Set && ((Set)value).toArray() != null){
						value = ((Set)value).toArray()[0];
					}
					valueo = operator.getAttributeValues().get(field.getName() + "Id");
					if(valueo != null && (valueo instanceof Long || valueo instanceof String) || valueo instanceof Integer){
						valueos = String.valueOf(valueo);
					}else if(valueo != null && valueo instanceof Set && ((Set)valueo).toArray() != null && ((Set)valueo).toArray()[0] != null){
						valueos = String.valueOf(((Set)valueo).toArray()[0]);
					}
				}
				if(StringUtils.isBlank(valueos))
					continue;
				
				condition.append(" and (");
				switch(SearchOperator.getOperator(Integer.valueOf(valueos))){
				case NOTEQUAL:
					if(type.equals("String") || type.equals("Long") || type.equals("Integer") || type.equals("Date") || type.equals("Time")){
						condition.append(entity.getAlias() + "." + field.getName() + " <> ? or " + entity.getAlias() + "." + field.getName() + " is null");
						values.add(value);
					}else{
						if(value != null && ((BaseStruct)value).getId() != null){
							if(!tables.containsKey(field.getName()))
								tables.put(field.getName(), new ArrayList<String>());
							String coltype = field.getColumn().getType();
							String seatype = field.getElementSearch().getSelectElement().getType();
							if(coltype.equals(seatype)){
								condition.append(field.getName() + ".id <> ?");
							}else{
								String refcol = seatype.substring(seatype.indexOf("(")+1, seatype.indexOf(")"));
								seatype = seatype.substring(0, seatype.indexOf("("));
								condition.append(field.getName() + ".id in (select a.id from " + coltype + "Struct a left join a." + refcol + " b where b.id <> ?)");
							}
							values.add(((BaseStruct)value).getId());
						}
					}
					break;
				case EQUAL:
					if(type.equals("String")){
						condition.append(entity.getAlias() + "." + field.getName() + " = ?");
						values.add(value);
					}else if(type.equals("Long") || type.equals("Integer") || type.equals("Date") || type.equals("Time")){
						if(value == null){
							
						}else{
							condition.append(entity.getAlias() + "." + field.getName() + " = ?");
							values.add(value);
						}
					}else if(type.equals("Boolean")){
						if(value == null){
							condition.append(entity.getAlias() + "." + field.getName() + " is null or " + entity.getAlias() + "." + field.getName() + " = ?");
							values.add(false);
						}else{
							condition.append(entity.getAlias() + "." + field.getName() + " = ?");
							values.add(value);
						}
					}else{
						if(value != null && ((BaseStruct)value).getId() != null){
							if(!tables.containsKey(field.getName()))
								tables.put(field.getName(), new ArrayList<String>());
							String coltype = field.getColumn().getType();
							String seatype = field.getElementSearch().getSelectElement().getType();
							if(coltype.equals(seatype)){
								condition.append(field.getName() + ".id = ?");
							}else{
								String refcol = seatype.substring(seatype.indexOf("(")+1, seatype.indexOf(")"));
								seatype = seatype.substring(0, seatype.indexOf("("));
								condition.append(field.getName() + ".id in (select a.id from " + coltype + "Struct a left join a." + refcol + " b where b.id = ?)");
							}
							values.add(((BaseStruct)value).getId());
						}
					}
					break;
				case LIKE:
					if(type.equals("String")){
						condition.append("lower(" + entity.getAlias() + "." + field.getName() + ") like lower(?)");
						values.add("%" + value + "%");
					}else if(type.equals("Long") || type.equals("Integer")){
						
					}
					break;
				case GREATER:
					if(type.equals("Long") || type.equals("Integer") || type.equals("Date") || type.equals("Time")){
						condition.append(entity.getAlias() + "." + field.getName() + " >= ?");
						values.add(value);
					}
					break;
				case LESS:
					if(type.equals("Long") || type.equals("Integer") || type.equals("Date") || type.equals("Time")){
						condition.append(entity.getAlias() + "." + field.getName() + " <= ?");
						values.add(value);
					}
					break;
				case BETWEEN:
					if(type.equals("Long") || type.equals("Integer") || type.equals("Date") || type.equals("Time")){
						if(value != null && value2 != null){
							condition.append(entity.getAlias() + "." + field.getName() + " >= ? and " + entity.getAlias() + "." + field.getName() + " <= ?");
							values.add(value);
							values.add(value2);
						}else if(value == null && value2 != null){
							condition.append(entity.getAlias() + "." + field.getName() + " <= ?");
							values.add(value2);
						}else if(value != null && value2 == null){
							condition.append(entity.getAlias() + "." + field.getName() + " >= ?");
							values.add(value);
						}
					}
					break;
				}
				condition.append(")");
				if(condition.lastIndexOf("and ()") == condition.length() -6){
					condition.delete(condition.lastIndexOf("and ()"), condition.length());
				}
			}
		}
		// Check fuzzy search
		StringBuffer fuzzyCondition = new StringBuffer();
		if(operator != null && StringUtils.isNotBlank(operator.getKeyword()) && StringUtils.isNotBlank(search.getEntity().getKeyword())){
			List<String> keywords = entity.getKeywords();
			if(keywords != null){
				for(String keyword : keywords){
					String table = keyword.substring(0, keyword.indexOf("."));
					String column = keyword.substring(keyword.indexOf(".") + 1);
					List<String> columns = null;
					if(tables.containsKey(table))
						columns = tables.get(table);
					else{
						columns = new ArrayList<String>();
						tables.put(table, columns);
					}
					if(!columns.contains(column))
						columns.add(column);
				}
			}
			if(tables != null){
				for(String table : tables.keySet()){
					for(String column : tables.get(table)){
						switch(SearchOperator.getOperator(Integer.valueOf(operator.getKeyword()))){
						case EQUAL:
							fuzzyCondition.append(" or " + table + "." + column + "= ?");
							values.add(search.getEntity().getKeyword());
							break;
						case LIKE:
							fuzzyCondition.append(" or lower(" + table + "." + column + ") like lower(?)");
							values.add("%" + search.getEntity().getKeyword() + "%");
							break;
						}
					}
				}
				if(fuzzyCondition.indexOf("or") > 0){
					fuzzyCondition.replace(fuzzyCondition.indexOf("or"), fuzzyCondition.indexOf("or") + 2, "and (");
					fuzzyCondition.append(")");
				}
				
			}
		}condition.append(fuzzyCondition.toString());
		// Join reference tables
		for(String table : tables.keySet()){
			if(!table.equals(entity.getAlias())){
				sql.append(" left join " + entity.getAlias() + "." + table + " " + table);
			}
		}
		sql.append(condition);
	}
	
	private void prepareHSQLOrder(AbstractSearchCommand<T> search, StringBuffer sql, List<Object> values){
		Entity entity = businessModelService.getEntityByClass(entityClass);
		StringBuffer orderby = new StringBuffer(" order by");
		if(search.getPropertiesSorted() != null && search.getPropertiesSorted().length > 0){
			for (String prop : search.getPropertiesSorted()) {
				String property = prop;
				String sort = "asc";
				if(prop.indexOf(":") > 0){
					property = prop.substring(0, property.indexOf(":"));
					sort = prop.substring(prop.indexOf(":") + 1);
				}
				if(sort.equals("asc") || sort.equals("desc"))
					orderby.append(" " + entity.getAlias() + "." + property + " " + sort);
			}
			if(orderby.lastIndexOf("order by") != (orderby.length() - 8)){
				sql.append(orderby);
				return;
			}
		}
		if(entity.getDefaultSort() != null){
			String property = entity.getDefaultSort();
			String sort = "asc";
			if(entity.getDefaultSort().indexOf(":") > 0){
				property = entity.getDefaultSort().substring(0, property.indexOf(":"));
				sort = entity.getDefaultSort().substring(entity.getDefaultSort().indexOf(":") + 1);
			}
			sql.append(orderby + " " + entity.getAlias() + "." + property + " " + sort);
		}
	}
}